﻿using System;
using System.Collections.Generic;
using System.Web;
using DNNToolkit.Modules.Glossary.Components;
using DotNetNuke.Common.Utilities;
using System.Linq;

namespace DNNToolkit.Modules.Glossary.Components
{
    public class DataManager
    {
        private readonly DNNToolkitDataContext context = new DNNToolkitDataContext(DotNetNuke.Common.Utilities.Config.GetConnectionString());
        #region "Glossary Methods"

        public GlossaryInfo GetGlossary(long glossaryID)
        {
            return context.GlossaryInfos.SingleOrDefault(s => s.GlossaryID == glossaryID);
        }
        public IQueryable<GlossaryInfo> ListGlossary(int moduleId)
        {
            return context.GlossaryInfos.Where(w => w.ModuleID == moduleId);
        }

        public IQueryable<GlossaryInfo> ListGlossaryByFilter(int moduleID, string filter)
        {
            return context.GlossaryInfos.Where(w => w.ModuleID == moduleID && (w.Term.ToLower().Contains(filter.ToLower()) || w.Definition.ToLower().Contains(filter.ToLower())));
        }

        public IQueryable<GlossaryInfo> ListGlossaryByCategory(int moduleId, long categoryid)
        {
            var gids = context.GlossaryCategories.Where(w => w.GlossaryCategoryDefinitionID == categoryid).Select(s => s.GlossaryInfo.GlossaryID);
            return context.GlossaryInfos.Where(w => w.ModuleID == moduleId && gids.Contains(w.GlossaryID));
        }

        ////List golssaries by filter and category
        public IQueryable<GlossaryInfo> ListGlossaryByFilterAndCategory(int moduleID, string filter, long glossaryCategoryDefinitionID)
        {
            var list = ListGlossaryByCategory(moduleID, glossaryCategoryDefinitionID);
            return list.Where(x => x.Term.ToLower().Contains(filter.ToLower()) || x.Definition.ToLower().Contains(filter.ToLower()));
        }

        ////List gossaries by letter
        public IQueryable<GlossaryInfo> ListGlossaryByLetter(int moduleID, string letter)
        {

            return context.GlossaryInfos.Where(w => w.ModuleID == moduleID && w.Term.ToLower().StartsWith(letter.ToLower()));
        }

        public long CreateGlossary(GlossaryInfo glossary)
        {
            context.GlossaryInfos.InsertOnSubmit(glossary);
            context.SubmitChanges();
            return Convert.ToInt64(glossary.GlossaryID);
        }

        public void UpdateGlossary(long glossaryID, string term, string definition, int moduleID, DateTime dateTimeStamp)
        {
            var glossary = context.GlossaryInfos.SingleOrDefault(s => s.GlossaryID == glossaryID);
            if (glossary != null)
            {
                glossary.Term = term;
                glossary.Definition = definition;
                glossary.ModuleID = moduleID;
                glossary.DateTimeStamp = dateTimeStamp;
                context.SubmitChanges();
            }
        }

        public void DeleteGlossary(long glossaryID)
        {
            DeleteGlossaryCategoryByGlossary(glossaryID);
            var glossary = context.GlossaryInfos.SingleOrDefault(s => s.GlossaryID == glossaryID);
            context.GlossaryInfos.DeleteOnSubmit(glossary);
            context.SubmitChanges();
        }

        #endregion

        #region "GlossaryCategory Methods"

        public GlossaryCategory GetGlossaryCategory(long glossaryCategoryID)
        {
            return context.GlossaryCategories.SingleOrDefault(s => s.GlossaryCategoryID == glossaryCategoryID);
        }

        public IQueryable<GlossaryCategory> ListGlossaryCategory()
        {
            return context.GlossaryCategories.AsQueryable();
        }

        public IQueryable<GlossaryCategory> GetGlossaryCategoryByGlossary(long glossaryID)
        {
            return context.GlossaryCategories.Where(w => w.GlossaryID == glossaryID);
        }

        public IQueryable<GlossaryCategory> GetGlossaryCategoryByGlossaryCategoryDefinition(long glossaryCategoryDefinitionID)
        {
            return context.GlossaryCategories.Where(w => w.GlossaryCategoryDefinitionID == glossaryCategoryDefinitionID);
        }

        public void CreateGlossaryCategory(GlossaryCategory glossaryCategory)
        {
            context.GlossaryCategories.InsertOnSubmit(glossaryCategory);
            context.SubmitChanges();
        }

        public void UpdateGlossaryCategory(long glossaryCategoryID, long glossaryID, long glossaryCategoryDefinitionID)
        {
            var glossaryCategory = context.GlossaryCategories.SingleOrDefault(s => s.GlossaryCategoryID == glossaryCategoryID);
            if (glossaryCategory != null)
            {
                glossaryCategory.GlossaryCategoryDefinitionID = glossaryCategoryDefinitionID;
                glossaryCategory.GlossaryID = glossaryID;
                context.SubmitChanges();
            }
        }

        public void DeleteGlossaryCategory(long glossaryCategoryID)
        {
            var glossaryCategory = context.GlossaryCategories.SingleOrDefault(s => s.GlossaryCategoryID == glossaryCategoryID);
            context.GlossaryCategories.DeleteOnSubmit(glossaryCategory);
            context.SubmitChanges();
        }

        public void DeleteGlossaryCategoryByGlossary(long glossaryID)
        {
            var glossaryCategories = context.GlossaryCategories.Where(s => s.GlossaryID == glossaryID);
            context.GlossaryCategories.DeleteAllOnSubmit(glossaryCategories);
            context.SubmitChanges();
        }

        #endregion

        #region "GlossaryCategoryDefinition Methods"

        public GlossaryCategoryDefinition GetGlossaryCategoryDefinition(long glossaryCategoryDefinitionID)
        {
            return context.GlossaryCategoryDefinitions.SingleOrDefault(s => s.GlossaryCategoryDefinitionID == glossaryCategoryDefinitionID);
        }
        public IQueryable<GlossaryCategoryDefinition> ListGlossaryCategoryDefinition()
        {
            return context.GlossaryCategoryDefinitions.AsQueryable();
        }

        public void CreateGlossaryCategoryDefinition(GlossaryCategoryDefinition definition)
        {
            context.GlossaryCategoryDefinitions.InsertOnSubmit(definition);
            context.SubmitChanges();
        }

        public void UpdateGlossaryCategoryDefinition(long glossaryCategoryDefinitionID, string categoryName)
        {
            var definition = context.GlossaryCategoryDefinitions.SingleOrDefault(s => s.GlossaryCategoryDefinitionID == glossaryCategoryDefinitionID);
            if (definition != null)
            {
                definition.CategoryName = categoryName;
                context.SubmitChanges();
            }
        }

        public void DeleteGlossaryCategoryDefinition(long glossaryCategoryDefinitionId)
        {
            var definition = context.GlossaryCategoryDefinitions.SingleOrDefault(s => s.GlossaryCategoryDefinitionID == glossaryCategoryDefinitionId);
            if (definition != null)
            {
                context.GlossaryCategories.DeleteAllOnSubmit(definition.GlossaryCategories);
                context.GlossaryCategoryDefinitions.DeleteOnSubmit(definition);
                context.SubmitChanges();
            }
        }

        #endregion
    }
}
